
import pymysql
from pymysql.cursors import DictCursor

class MySQLSSHClient:
    def __init__(
        self,
        mysql_host: str = "rm-gw8a4drz5t1dde7g3.mysql.germany.rds.aliyuncs.com" ,
        mysql_port: int = 3306,
        mysql_user: str = "root",
        mysql_password: str = "suKeepgo123@cys",
        mysql_db: str = "cfx_t",

        # mysql_host: str = "localhost" ,
        # mysql_port: int = 3306,
        # mysql_user: str = "root",
        # mysql_password: str = "1q2w3e4r5t@",
        # mysql_db: str = "cfx_t"
    ):
        self.mysql_host = mysql_host
        self.mysql_port = mysql_port
        self.mysql_user = mysql_user
        self.mysql_password = mysql_password
        self.mysql_db = mysql_db

        self.db_conn = None


    def connect(self) -> bool:
        try:

            # 连接MySQL
            self.db_conn = pymysql.connect(
                host=self.mysql_host,
                port=self.mysql_port,
                user=self.mysql_user,
                password=self.mysql_password,
                database=self.mysql_db,
                charset="utf8mb4",
                cursorclass=DictCursor
            )
            #print("MySQL连接成功")
            return True
        except Exception as e:
            print(f"连接失败: {str(e)}")
            self.close()  # 确保资源释放
            return False

    def query(self, sql: str):
        """
        执行查询SQL
        
        :param sql: SQL查询语句
        :return: 元组(查询结果数据, 列名列表)
        """
        if not self.db_conn:
            raise Exception("请先调用connect()建立连接")

        try:
            with self.db_conn.cursor() as cursor:
                cursor.execute(sql)
                # 获取查询结果
                data = cursor.fetchall()
                # 获取列名
                columns = [desc[0] for desc in cursor.description]
                return data, columns
        except pymysql.MySQLError as e:
            print(f"查询失败: {str(e)}")
            raise  # 抛出异常让调用者处理

    def execSql(self, sql: str):
        """
        执行查询SQL

        :param sql: SQL查询语句
        :return: 元组(查询结果数据, 列名列表)
        """
        if not self.db_conn:
            raise Exception("请先调用connect()建立连接")

        try:
            with self.db_conn.cursor() as cursor:
                cursor.execute(sql)

                # 获取查询结果
                data = cursor.fetchall()
                self.db_conn.commit()
                return data
        except pymysql.MySQLError as e:
            print(f"查询失败: {str(e)}")
            raise  # 抛出异常让调用者处理

    def close(self) -> None:
        """关闭所有连接"""
        # 关闭MySQL连接
        if self.db_conn and self.db_conn.open:
            self.db_conn.close()
            #print("MySQL连接已关闭")


    def __enter__(self):
        """支持上下文管理器"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器退出时关闭连接"""
        self.close()


# 使用示例
if __name__ == "__main__":

    # 方法1: 常规使用
    client = MySQLSSHClient()

    with open('sql.txt', 'r', encoding='utf-8') as f:
        #逐行遍历文件，每次迭代返回一行（包含末尾的换行符 '\n'）
        for line in f:
            # 处理每行内容（示例：打印行内容，去除末尾换行符可用 strip()）
            print(line.strip())  # strip() 移除首尾空白（包括 '\n'）

            if client.connect():
                try:
                    data = client.execSql(line.strip())
                    print("执行结果:", data)
                finally:
                    client.close()


